﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace LibFriesland_Campina_CRM
{
    class DalNaShop
    {
        //Lay danh ca lam viec cua NA theo Supervisor
        public static List<BizNaShop> getShiftNAbySup(string pBy_sup)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var naShifts = from item in db.NAShop
                               where item.NA.BySup == pBy_sup
                               select new BizNaShop(item.ByNA,item.NA.Name, item.ByShift, item.Shift.Name, item.ByShop, item.Shop.Name);
                List<BizNaShop> results = naShifts.ToList();
                return results;
            }
            catch(Exception ex)
            {
                return null;
            }
        }

        //Lay danh sach ca lam viec cua NA theo Shop, theo Sup
        public static List<BizNaShop> getShiftNAbyShopSup(string pBy_shop, string pBy_sup)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var naShifts = from item in db.NAShop
                               where item.NA.BySup == pBy_sup && item.ByShop == pBy_shop
                               select new BizNaShop(item.ByNA,item.NA.Name, item.ByShift, item.Shift.Name, item.ByShop, item.Shop.Name);
                return naShifts.ToList();
            }
            catch(Exception ex)
            {
                return null;
            }
        }

        //Lay danh sach tat ca Shop ma NA lam viec theo Supervisor
        public static List<BizShop> getShopNAbySup(string pBy_sup)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var shops = from item in db.NAShop
                            where item.NA.BySup == pBy_sup
                            select new BizShop(item.ByShop, item.Shop.Name);

                List<BizShop> results = shops.Distinct().ToList();
                results.Sort(ShopSort.shopCharIDComparison);
                return results;
            }
            catch(Exception ex)
            {
                return null;
            }
        }

        #region Dung cho Webservice
        //Lay danh sach cac ca lam viec cua tat ca NA
        public static List<BizNaShop> getAll()
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            var naShops = from naShop in db.NAShop
                          select new BizNaShop(naShop.ByNA, naShop.ByShift, naShop.Shift.Name, naShop.ByShop);
            return naShops.ToList();
        }

        //Lay danh sach ca lam viec cua NA
        public static List<BizNaShop> getShiftbyNA(string pNa_charid)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var naShops = from naShop in db.NAShop
                              where naShop.ByNA == pNa_charid
                              select new BizNaShop(naShop.ByNA, naShop.ByShift, Thuvien.ConvertToUnSign(naShop.Shift.Name), naShop.ByShop);
                return naShops.ToList();
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        //Lay danh sach Shop ma NA lam viec
        public static List<BizShop> getShopbyNA(string pNa_charid)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var shops = from s in db.NAShop
                            where s.ByNA == pNa_charid
                            select new BizShop(s.ByShop, s.Shop.Name);
                List<BizShop> results = shops.Distinct().ToList();
                return results;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        //Lay danh sach ca lam viec cua NA tai mot shop
        public static List<BizShift> getShiftbyNAShop(string pNa_charid, string pShop_charid)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var shifts = from naShop in db.NAShop
                             where naShop.ByNA == pNa_charid && naShop.ByShop == pShop_charid
                             select new BizShift(naShop.Shift.ShiftCharID, Thuvien.ConvertToUnSign(naShop.Shift.Name));
                return shifts.ToList();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion
    }
}
